package com.baseweb.webadmin.core.model.mapper;

import com.baseweb.webadmin.core.model.pojo.ByOpenCity;
import com.baseweb.webadmin.core.model.pojo.ByOpenCityExample;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectKey;
import org.apache.ibatis.annotations.Update;

public interface ByOpenCityMapper {
    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table by_open_city
     *
     * @mbggenerated Sat Feb 18 10:14:51 CST 2017
     */
    int countByExample(ByOpenCityExample example);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table by_open_city
     *
     * @mbggenerated Sat Feb 18 10:14:51 CST 2017
     */
    int deleteByExample(ByOpenCityExample example);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table by_open_city
     *
     * @mbggenerated Sat Feb 18 10:14:51 CST 2017
     */
    @Delete({
        "delete from by_open_city",
        "where id = #{id,jdbcType=INTEGER}"
    })
    int deleteByPrimaryKey(Integer id);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table by_open_city
     *
     * @mbggenerated Sat Feb 18 10:14:51 CST 2017
     */
    @Insert({
        "insert into by_open_city (province, city, ",
        "area, status, create_time, ",
        "update_time, ",
        "user_id, open_type)",
        "values (#{province,jdbcType=VARCHAR}, #{city,jdbcType=VARCHAR}, ",
        "#{area,jdbcType=VARCHAR}, #{status,jdbcType=INTEGER}, #{createTime,jdbcType=TIMESTAMP,typeHandler=com.baseweb.mybatis.typehandler.JodaDateTime2TimestampTypeHandler}, ",
        "#{updateTime,jdbcType=TIMESTAMP,typeHandler=com.baseweb.mybatis.typehandler.JodaDateTime2TimestampTypeHandler}, ",
        "#{userId,jdbcType=INTEGER}, #{openType,jdbcType=INTEGER})"
    })
    @SelectKey(statement="SELECT LAST_INSERT_ID()", keyProperty="id", before=false, resultType=Integer.class)
    int insert(ByOpenCity record);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table by_open_city
     *
     * @mbggenerated Sat Feb 18 10:14:51 CST 2017
     */
    int insertSelective(ByOpenCity record);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table by_open_city
     *
     * @mbggenerated Sat Feb 18 10:14:51 CST 2017
     */
    List<ByOpenCity> selectByExample(ByOpenCityExample example);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table by_open_city
     *
     * @mbggenerated Sat Feb 18 10:14:51 CST 2017
     */
    @Select({
        "select",
        "id, province, city, area, status, create_time, update_time, user_id, open_type",
        "from by_open_city",
        "where id = #{id,jdbcType=INTEGER}"
    })
    @ResultMap("BaseResultMap")
    ByOpenCity selectByPrimaryKey(Integer id);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table by_open_city
     *
     * @mbggenerated Sat Feb 18 10:14:51 CST 2017
     */
    int updateByExampleSelective(@Param("record") ByOpenCity record, @Param("example") ByOpenCityExample example);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table by_open_city
     *
     * @mbggenerated Sat Feb 18 10:14:51 CST 2017
     */
    int updateByExample(@Param("record") ByOpenCity record, @Param("example") ByOpenCityExample example);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table by_open_city
     *
     * @mbggenerated Sat Feb 18 10:14:51 CST 2017
     */
    int updateByPrimaryKeySelective(ByOpenCity record);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table by_open_city
     *
     * @mbggenerated Sat Feb 18 10:14:51 CST 2017
     */
    @Update({
        "update by_open_city",
        "set province = #{province,jdbcType=VARCHAR},",
          "city = #{city,jdbcType=VARCHAR},",
          "area = #{area,jdbcType=VARCHAR},",
          "status = #{status,jdbcType=INTEGER},",
          "create_time = #{createTime,jdbcType=TIMESTAMP,typeHandler=com.baseweb.mybatis.typehandler.JodaDateTime2TimestampTypeHandler},",
          "update_time = #{updateTime,jdbcType=TIMESTAMP,typeHandler=com.baseweb.mybatis.typehandler.JodaDateTime2TimestampTypeHandler},",
          "user_id = #{userId,jdbcType=INTEGER},",
          "open_type = #{openType,jdbcType=INTEGER}",
        "where id = #{id,jdbcType=INTEGER}"
    })
    int updateByPrimaryKey(ByOpenCity record);

    /**
     * 查询所有省份
     */
    @Select({
            "select",
            "distinct province ",
            "from by_open_city",
            "where 1=1"
    })
    @ResultMap("BaseResultMap")
    List<ByOpenCity> findAllProvince();

    /**
     * 根据省份查询所有市
     */
    @Select({
            "select",
            "distinct city ",
            "from by_open_city",
            "where province = #{0}"
    })
    @ResultMap("BaseResultMap")
    List<ByOpenCity> findAllCityByProvince(String province);
    /**
     * 根据市查询所有区
     */
    @Select({
            "select",
            "distinct area ",
            "from by_open_city",
            "where city = #{0}"
    })
    @ResultMap("BaseResultMap")
    List<ByOpenCity> findAllAreaByCity(String city);

    /**
     * 查询所有开通省市区 查询条件
     */
    @Select({
            "select",
            "DISTINCT province,city ",
            "from by_open_city ",
            "where status = 1 and (province = #{0} or #{0} = '') or (city = #{0} or #{0} = '') ",
            "group by province,city"
    })
    @ResultMap("BaseResultMap")
    List<ByOpenCity> findAllProvinceCityListByWhere(String where);

    /**
     * 根据角用户查询开通的城市
     */
    @Select({
            "select",
            "distinct city ",
            "from by_open_city,role_opencity ",
            "where by_open_city.id = role_opencity.opencity_id and role_opencity.user_id = #{0}"
    })
    @ResultMap("BaseResultMap")
    List<ByOpenCity> findAllCityByUserId(int userId);

    /**
     * 根据角用户查询开通的区域
     */
    @Select({
            "select",
            "distinct area ",
            "from by_open_city,role_opencity ",
            "where by_open_city.id = role_opencity.opencity_id and role_opencity.user_id = #{0}"
    })
    @ResultMap("BaseResultMap")
    List<ByOpenCity> findAllAreaByUserId(int userId);

    @Select({
            "select",
            "province, city, area ",
            "from by_open_city,role_opencity ",
            "where by_open_city.id = role_opencity.opencity_id and role_opencity.user_id = #{0}"
    })
    @ResultMap("BaseResultMap")
    public List<ByOpenCity> findOpenCityByUserId(int userId);

    /**
     * 根据角色关联查询所有省市区 查询条件
     */
    @Select({
            "select",
            "id,province,city,area,ro.opencity_id as open_type ",
            "from by_open_city ",
            "LEFT JOIN ",
            "(select opencity_id from role_opencity where user_id = #{0}) as ro ",
            "on by_open_city.id = ro.opencity_id ",
            "where status = 1 and (province = #{1} or #{1} = '') or (city = #{1} or #{1} = '') or (area = #{1} or #{1} = '') ",
            "group by province,city,area"
    })
    @ResultMap("BaseResultMap")
    List<ByOpenCity> findAllListByWhere(int roleId,String where);

    /**
     * 查询所有省市区列表带分页和查询条件
     */
    @Select({
            "select",
            "id,province,city,area ",
            "from by_open_city ",
            "where status = 1 and (province = #{0} or #{0} = '') or (city = #{0} or #{0} = '') or (area = #{0} or #{0} = '') ",
            "LIMIT #{1},#{2}"
    })
    @ResultMap("BaseResultMap")
    List<ByOpenCity> findAllListPageByWhere(String where,int page,int pageNum);

    /**
     * 查询所有省市区列表带分页和查询条件count
     */
    @Select({
            "select",
            "count(*) ",
            "from by_open_city ",
            "where status = 1 and (province = #{0} or #{0} = '') or (city = #{0} or #{0} = '') or (area = #{0} or #{0} = '') "
    })
    int countAllListPageByWhere(String where);

    /**
     * 根据省市区查询
     */
    @Select({
            "select",
            " * ",
            "from by_open_city ",
            "where status = 1 and province = #{0} and city = #{1} and area = #{2}  "
    })
    @ResultMap("BaseResultMap")
    ByOpenCity findByProvinceCityArea(String province,String city,String area);

    /**
     * 根据市查询区
     */
    @Select({
            "select",
            " * ",
            "from by_open_city ",
            "where status = 1 and city = #{0}  "
    })
    @ResultMap("BaseResultMap")
    List<ByOpenCity> findAreaByCity(String city);

    /**
     * 插入角色服务城市关系表
     */
    @Insert({
            "insert into role_opencity (user_id, opencity_id) ",
            "values (#{0}, #{1}) "
    })
    @SelectKey(statement="SELECT LAST_INSERT_ID()", keyProperty="id", before=false, resultType=Integer.class)
    int insertRoleOpenCity(int userId,int openId);

    /**
     * 根据roleId 删除关系表
     */
    @Delete({
            "delete from role_opencity",
            "where user_id = #{0}"
    })
    int deleteRoleOpenCityByRoleId(int userId);

    /**
     * 根据openCityId 删除关系表
     */
    @Delete({
            "delete from role_opencity",
            "where opencity_id = #{0}"
    })
    int deleteRoleOpenCityByOpenCityId(int openCityId);

    /**
     * 查询用户关联的所有省市区列表带分页和查询条件
     */
    @Select({
            "select",
            "id,province,city,area ",
            "from user_opencity ",
            "where status = 1 and user_id = #{0} " +
                    "and (province = #{1} or #{1} = '') or (city = #{1} or #{1} = '') or (area = #{1} or #{1} = '') ",
            "LIMIT #{2},#{3}"
    })
    @ResultMap("BaseResultMap")
    List<ByOpenCity> findAllUserListPageByWhere(int userId,String where,int page,int pageNum);

    /**
     * 查询用户关联的所有省市区列表带分页和查询条件count
     */
    @Select({
            "select",
            "count(*) ",
            "from user_opencity ",
            "where status = 1 and user_id = #{0} " +
                    "and (province = #{1} or #{1} = '') or (city = #{1} or #{1} = '') or (area = #{1} or #{1} = '') "
    })
    int countAllUesrListPageByWhere(int userId,String where);

    /**
     * 添加用户服务城市
     */
    @Insert({
            "insert into user_opencity (province, city, ",
            "area, status, create_time, ",
            "update_time, ",
            "user_id)",
            "values (#{province,jdbcType=VARCHAR}, #{city,jdbcType=VARCHAR}, ",
            "#{area,jdbcType=VARCHAR}, #{status,jdbcType=INTEGER}, #{createTime,jdbcType=TIMESTAMP,typeHandler=com.baseweb.mybatis.typehandler.JodaDateTime2TimestampTypeHandler}, ",
            "#{updateTime,jdbcType=TIMESTAMP,typeHandler=com.baseweb.mybatis.typehandler.JodaDateTime2TimestampTypeHandler}, ",
            "#{userId,jdbcType=INTEGER})"
    })
    @SelectKey(statement="SELECT LAST_INSERT_ID()", keyProperty="id", before=false, resultType=Integer.class)
    int insertUserOpenCity(ByOpenCity record);

    /**
     * 根据userId 删除服务城市
     */
    @Delete({
            "delete from user_opencity",
            "where user_id = #{0}"
    })
    int deleteUserOpenCityByRoleId(int userId);

    /**
     * 根据userid省市区查询用户服务城市
     */
    @Select({
            "select",
            " * ",
            "from user_opencity ",
            "where status = 1 and province = #{0} and city = #{1} and area = #{2} and user_id = #{3}  "
    })
    @ResultMap("BaseResultMap")
    ByOpenCity findByUserIdProvinceCityArea(String province,String city,String area,int userId);

    /**
     * 根据userid用户服务城市
     */
    @Select({
            "select",
            " * ",
            "from user_opencity ",
            "where status = 1 and user_id = #{0}  "
    })
    @ResultMap("BaseResultMap")
    List<ByOpenCity> findUserCityByUserId(int userId);

    /**
     * 根据id删除服务城市
     */
    @Delete({
            "delete from user_opencity",
            "where id = #{0}"
    })
    int deleteUserOpenCityById(int id);
}